using Dapper;
using Lzfy_His_Service.Models;
using Microsoft.Data.SqlClient;
using System.Data;
using System.Text;

namespace Lzfy_His_Service.Repositories
{
    /// <summary>
    /// 银行信息仓储实现
    /// </summary>
    public class BankInfoRepository : IBankInfoRepository
    {
        private readonly string _connectionString;

        public BankInfoRepository(IConfiguration configuration)
        {
            _connectionString = configuration.GetConnectionString("DefaultConnection") 
                ?? throw new ArgumentNullException(nameof(configuration), "数据库连接字符串未配置");
        }

        /// <summary>
        /// 获取数据库连接
        /// </summary>
        /// <returns></returns>
        private IDbConnection GetConnection()
        {
            return new SqlConnection(_connectionString);
        }

        /// <summary>
        /// 获取银行信息列表（分页）
        /// </summary>
        public async Task<(List<BankInfo> banks, int totalCount)> GetBankInfoListAsync(
            string? keyword = null, 
            bool? isCustomOnly = null, 
            bool? isActiveOnly = true, 
            int pageIndex = 1, 
            int pageSize = 20)
        {
            using var connection = GetConnection();
            
            var whereConditions = new List<string>();
            var parameters = new DynamicParameters();

            if (!string.IsNullOrWhiteSpace(keyword))
            {
                whereConditions.Add("(b.StandardName LIKE @Keyword OR b.ShortName LIKE @Keyword OR b.EnglishName LIKE @Keyword)");
                parameters.Add("@Keyword", $"%{keyword}%");
            }

            if (isCustomOnly.HasValue)
            {
                whereConditions.Add("b.IsCustom = @IsCustom");
                parameters.Add("@IsCustom", isCustomOnly.Value);
            }

            if (isActiveOnly.HasValue)
            {
                whereConditions.Add("b.IsActive = @IsActive");
                parameters.Add("@IsActive", isActiveOnly.Value);
            }

            var whereClause = whereConditions.Count > 0 ? $"WHERE {string.Join(" AND ", whereConditions)}" : "";

            // 查询总数
            var countSql = $@"
                SELECT COUNT(1) 
                FROM BankInfo b 
                {whereClause}";

            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 查询数据
            parameters.Add("@Offset", (pageIndex - 1) * pageSize);
            parameters.Add("@PageSize", pageSize);

            var dataSql = $@"
                SELECT b.*, 
                       STRING_AGG(bm.BinCode, ',') AS BinCodes,
                       (
                           SELECT STRING_AGG(CONCAT(na.AliasName, '|', na.AliasType), ',')
                           FROM BankNameAlias na 
                           WHERE na.BankId = b.Id AND na.IsActive = 1
                       ) AS Aliases
                FROM BankInfo b
                LEFT JOIN BankBinMapping bm ON b.Id = bm.BankId AND bm.IsActive = 1
                {whereClause}
                GROUP BY b.Id, b.StandardName, b.ShortName, b.EnglishName, b.BankCode, 
                         b.IsCustom, b.IsActive, b.MinCardLength, b.MaxCardLength, 
                         b.SupportedCardTypes, b.Description, b.CreatedAt, b.UpdatedAt, 
                         b.CreatedBy, b.UpdatedBy
                ORDER BY b.IsCustom DESC, b.StandardName
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            var banks = await connection.QueryAsync<BankInfo>(dataSql, parameters);

            // 处理BIN码和别名
            foreach (var bank in banks)
            {
                // BinMappings will be loaded separately in the multi-query
                bank.BinMappings = new List<BankBinMapping>();

                // NameAliases will be loaded separately in the multi-query
                bank.NameAliases = new List<BankNameAlias>();
            }

            return (banks.ToList(), totalCount);
        }

        /// <summary>
        /// 根据ID获取银行信息
        /// </summary>
        public async Task<BankInfo?> GetBankInfoByIdAsync(int id)
        {
            using var connection = GetConnection();
            
            var sql = @"
                SELECT * FROM BankInfo WHERE Id = @Id;
                SELECT * FROM BankBinMapping WHERE BankId = @Id AND IsActive = 1;
                SELECT * FROM BankNameAlias WHERE BankId = @Id AND IsActive = 1;";

            using var multi = await connection.QueryMultipleAsync(sql, new { Id = id });
            
            var bank = await multi.ReadSingleOrDefaultAsync<BankInfo>();
            if (bank != null)
            {
                bank.BinMappings = (await multi.ReadAsync<BankBinMapping>()).ToList();
                bank.NameAliases = (await multi.ReadAsync<BankNameAlias>()).ToList();
            }

            return bank;
        }

        /// <summary>
        /// 根据标准名称获取银行信息
        /// </summary>
        public async Task<BankInfo?> GetBankInfoByStandardNameAsync(string standardName)
        {
            using var connection = GetConnection();
            
            var sql = "SELECT * FROM BankInfo WHERE StandardName = @StandardName AND IsActive = 1";
            return await connection.QuerySingleOrDefaultAsync<BankInfo>(sql, new { StandardName = standardName });
        }

        /// <summary>
        /// 创建银行信息
        /// </summary>
        public async Task<int> CreateBankInfoAsync(BankInfo bankInfo)
        {
            using var connection = GetConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                var sql = @"
                    INSERT INTO BankInfo (StandardName, ShortName, EnglishName, BankCode, CardLengthRule, IsCustom, IsActive,
                                         SupportedCardTypes, Description, CreatedAt, CreatedBy, UpdatedAt, UpdatedBy, Remark)
                    VALUES (@StandardName, @ShortName, @EnglishName, @BankCode, @CardLengthRule, @IsCustom, @IsActive,
                            @SupportedCardTypes, @Description, @CreatedAt, @CreatedBy, @UpdatedAt, @UpdatedBy, @Remark);
                    SELECT CAST(SCOPE_IDENTITY() as int);";

                var bankId = await connection.QuerySingleAsync<int>(sql, bankInfo, transaction);
                bankInfo.Id = bankId;

                // 插入BIN码映射
                if (bankInfo.BinMappings?.Any() == true)
                {
                    await UpdateBankBinMappingsAsync(connection, transaction, bankId, 
                        bankInfo.BinMappings.Select(x => x.BinCode).ToList(), bankInfo.CreatedBy);
                }

                // 插入别名
                if (bankInfo.NameAliases?.Any() == true)
                {
                    await UpdateBankAliasesAsync(connection, transaction, bankId, 
                        bankInfo.NameAliases.Select(x => new BankAliasRequest 
                        { 
                            AliasName = x.AliasName, 
                            AliasType = x.AliasType 
                        }).ToList(), bankInfo.CreatedBy);
                }

                // 记录变更日志
                await LogChangeAsync(connection, transaction, new BankInfoChangeLog
                {
                    BankId = bankId,
                    ChangeType = "CREATE",
                    TableName = "BankInfo",
                    FieldName = "ALL",
                    NewValue = $"创建银行: {bankInfo.StandardName}",
                    CreatedAt = DateTime.Now,
                    CreatedBy = bankInfo.CreatedBy
                });

                transaction.Commit();
                return bankId;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 更新银行信息
        /// </summary>
        public async Task<bool> UpdateBankInfoAsync(BankInfo bankInfo)
        {
            using var connection = GetConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                // 获取原始数据用于记录变更
                var originalBank = await GetBankInfoByIdAsync(bankInfo.Id);
                if (originalBank == null) return false;

                var sql = @"
                    UPDATE BankInfo 
                    SET StandardName = @StandardName, ShortName = @ShortName, EnglishName = @EnglishName, 
                        BankCode = @BankCode, IsActive = @IsActive, MinCardLength = @MinCardLength, 
                        MaxCardLength = @MaxCardLength, SupportedCardTypes = @SupportedCardTypes, 
                        Description = @Description, UpdatedAt = @UpdatedAt, UpdatedBy = @UpdatedBy
                    WHERE Id = @Id";

                bankInfo.UpdatedAt = DateTime.Now;
                var result = await connection.ExecuteAsync(sql, bankInfo, transaction);

                // 记录字段变更
                await LogFieldChangesAsync(connection, transaction, originalBank, bankInfo);

                transaction.Commit();
                return result > 0;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 删除银行信息（软删除）
        /// </summary>
        public async Task<bool> DeleteBankInfoAsync(int id)
        {
            using var connection = GetConnection();
            
            var sql = "UPDATE BankInfo SET IsActive = 0, UpdatedAt = @UpdatedAt WHERE Id = @Id";
            var result = await connection.ExecuteAsync(sql, new { Id = id, UpdatedAt = DateTime.Now });
            
            return result > 0;
        }

        /// <summary>
        /// 根据BIN码获取银行信息
        /// </summary>
        public async Task<BankInfo?> GetBankInfoByBinCodeAsync(string binCode)
        {
            using var connection = GetConnection();
            
            var sql = @"
                SELECT b.* 
                FROM BankInfo b
                INNER JOIN BankBinMapping bm ON b.Id = bm.BankId
                WHERE bm.BinCode = @BinCode AND b.IsActive = 1 AND bm.IsActive = 1";
            
            return await connection.QuerySingleOrDefaultAsync<BankInfo>(sql, new { BinCode = binCode });
        }

        /// <summary>
        /// 根据别名获取银行信息
        /// </summary>
        public async Task<BankInfo?> GetBankInfoByAliasAsync(string aliasName)
        {
            using var connection = GetConnection();
            
            var sql = @"
                SELECT b.* 
                FROM BankInfo b
                INNER JOIN BankNameAlias na ON b.Id = na.BankId
                WHERE na.AliasName = @AliasName AND b.IsActive = 1 AND na.IsActive = 1";
            
            return await connection.QuerySingleOrDefaultAsync<BankInfo>(sql, new { AliasName = aliasName });
        }

        /// <summary>
        /// 搜索银行信息
        /// </summary>
        public async Task<List<BankInfo>> SearchBankInfoAsync(string keyword, int limit = 10)
        {
            using var connection = GetConnection();
            
            var sql = @"
                SELECT TOP(@Limit) b.*
                FROM BankInfo b
                LEFT JOIN BankNameAlias na ON b.Id = na.BankId AND na.IsActive = 1
                WHERE b.IsActive = 1 
                  AND (b.StandardName LIKE @Keyword 
                       OR b.EnglishName LIKE @Keyword 
                       OR b.ShortName LIKE @Keyword
                       OR na.AliasName LIKE @Keyword)
                GROUP BY b.Id, b.StandardName, b.ShortName, b.EnglishName, b.BankCode, 
                         b.IsCustom, b.IsActive, b.MinCardLength, b.MaxCardLength, 
                         b.SupportedCardTypes, b.Description, b.CreatedAt, b.UpdatedAt, 
                         b.CreatedBy, b.UpdatedBy
                ORDER BY b.IsCustom DESC, b.StandardName";
            
            var result = await connection.QueryAsync<BankInfo>(sql, new { Keyword = $"%{keyword}%", Limit = limit });
            return result.ToList();
        }

        /// <summary>
        /// 获取所有启用的银行列表
        /// </summary>
        public async Task<List<BankInfo>> GetAllActiveBanksAsync()
        {
            using var connection = GetConnection();
            
            var sql = "SELECT * FROM BankInfo WHERE IsActive = 1 ORDER BY IsCustom DESC, StandardName";
            var result = await connection.QueryAsync<BankInfo>(sql);
            return result.ToList();
        }

        /// <summary>
        /// 检查银行名称是否已存在
        /// </summary>
        public async Task<bool> IsBankNameExistsAsync(string standardName, int? excludeId = null)
        {
            using var connection = GetConnection();
            
            var sql = "SELECT COUNT(1) FROM BankInfo WHERE StandardName = @StandardName";
            var parameters = new Dapper.DynamicParameters();
            parameters.Add("@StandardName", standardName);
            
            if (excludeId.HasValue)
            {
                sql += " AND Id != @ExcludeId";
                parameters.Add("@ExcludeId", excludeId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }

        /// <summary>
        /// 检查BIN码是否已存在
        /// </summary>
        public async Task<bool> IsBinCodeExistsAsync(string binCode, int? excludeBankId = null)
        {
            using var connection = GetConnection();
            
            var sql = "SELECT COUNT(1) FROM BankBinMapping WHERE BinCode = @BinCode AND IsActive = 1";
            var parameters = new Dapper.DynamicParameters();
            parameters.Add("@BinCode", binCode);
            
            if (excludeBankId.HasValue)
            {
                sql += " AND BankId != @ExcludeBankId";
                parameters.Add("@ExcludeBankId", excludeBankId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }

        /// <summary>
        /// 检查别名是否已存在
        /// </summary>
        public async Task<bool> IsAliasExistsAsync(string aliasName, int? excludeBankId = null)
        {
            using var connection = GetConnection();
            
            var sql = "SELECT COUNT(1) FROM BankNameAlias WHERE AliasName = @AliasName AND IsActive = 1";
            var parameters = new Dapper.DynamicParameters();
            parameters.Add("@AliasName", aliasName);
            
            if (excludeBankId.HasValue)
            {
                sql += " AND BankId != @ExcludeBankId";
                parameters.Add("@ExcludeBankId", excludeBankId.Value);
            }
            
            var count = await connection.QuerySingleAsync<int>(sql, parameters);
            return count > 0;
        }

        /// <summary>
        /// 批量更新BIN码映射
        /// </summary>
        public async Task<bool> UpdateBankBinMappingsAsync(int bankId, List<string> binCodes, string? updatedBy = null)
        {
            using var connection = GetConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                await UpdateBankBinMappingsAsync(connection, transaction, bankId, binCodes, updatedBy);
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 批量更新银行别名
        /// </summary>
        public async Task<bool> UpdateBankAliasesAsync(int bankId, List<BankAliasRequest> aliases, string? updatedBy = null)
        {
            using var connection = GetConnection();
            using var transaction = connection.BeginTransaction();
            
            try
            {
                await UpdateBankAliasesAsync(connection, transaction, bankId, aliases, updatedBy);
                transaction.Commit();
                return true;
            }
            catch
            {
                transaction.Rollback();
                throw;
            }
        }

        /// <summary>
        /// 记录变更日志
        /// </summary>
        public async Task<bool> LogChangeAsync(BankInfoChangeLog log)
        {
            using var connection = GetConnection();
            return await LogChangeAsync(connection, null, log);
        }

        /// <summary>
        /// 获取银行变更日志
        /// </summary>
        public async Task<(List<BankInfoChangeLog> logs, int totalCount)> GetChangeLogsAsync(
            int? bankId = null, 
            int pageIndex = 1, 
            int pageSize = 20)
        {
            using var connection = GetConnection();
            
            var whereClause = bankId.HasValue ? "WHERE BankId = @BankId" : "";
            var parameters = new DynamicParameters();
            if (bankId.HasValue)
            {
                parameters.Add("@BankId", bankId.Value);
            }

            // 查询总数
            var countSql = $"SELECT COUNT(1) FROM BankInfoChangeLog {whereClause}";
            var totalCount = await connection.QuerySingleAsync<int>(countSql, parameters);

            // 查询数据
            parameters.Add("@Offset", (pageIndex - 1) * pageSize);
            parameters.Add("@PageSize", pageSize);

            var dataSql = $@"
                SELECT * FROM BankInfoChangeLog 
                {whereClause}
                ORDER BY CreatedAt DESC
                OFFSET @Offset ROWS FETCH NEXT @PageSize ROWS ONLY";

            var logs = await connection.QueryAsync<BankInfoChangeLog>(dataSql, parameters);
            return (logs.ToList(), totalCount);
        }

        #region 私有方法

        /// <summary>
        /// 更新BIN码映射（内部方法）
        /// </summary>
        private async Task UpdateBankBinMappingsAsync(IDbConnection connection, IDbTransaction? transaction, 
            int bankId, List<string> binCodes, string? updatedBy)
        {
            // 先禁用所有现有的BIN码映射
            var disableSql = "UPDATE BankBinMapping SET IsActive = 0, UpdatedAt = @UpdatedAt WHERE BankId = @BankId";
            await connection.ExecuteAsync(disableSql, new { BankId = bankId, UpdatedAt = DateTime.Now }, transaction);

            // 插入新的BIN码映射
            if (binCodes?.Any() == true)
            {
                var insertSql = @"
                    INSERT INTO BankBinMapping (BankId, BinCode, IsActive, CreatedAt, CreatedBy)
                    VALUES (@BankId, @BinCode, 1, @CreatedAt, @CreatedBy)";

                var mappings = binCodes.Select(bin => new
                {
                    BankId = bankId,
                    BinCode = bin,
                    CreatedAt = DateTime.Now,
                    CreatedBy = updatedBy
                });

                await connection.ExecuteAsync(insertSql, mappings, transaction);
            }
        }

        /// <summary>
        /// 更新银行别名（内部方法）
        /// </summary>
        private async Task UpdateBankAliasesAsync(IDbConnection connection, IDbTransaction? transaction, 
            int bankId, List<BankAliasRequest> aliases, string? updatedBy)
        {
            // 先禁用所有现有的别名
            var disableSql = "UPDATE BankNameAlias SET IsActive = 0, UpdatedAt = @UpdatedAt WHERE BankId = @BankId";
            await connection.ExecuteAsync(disableSql, new { BankId = bankId, UpdatedAt = DateTime.Now }, transaction);

            // 插入新的别名
            if (aliases?.Any() == true)
            {
                var insertSql = @"
                    INSERT INTO BankNameAlias (BankId, AliasName, AliasType, IsActive, CreatedAt, CreatedBy)
                    VALUES (@BankId, @AliasName, @AliasType, 1, @CreatedAt, @CreatedBy)";

                var nameAliases = aliases.Select(alias => new
                {
                    BankId = bankId,
                    AliasName = alias.AliasName,
                    AliasType = alias.AliasType ?? "common",
                    CreatedAt = DateTime.Now,
                    CreatedBy = updatedBy
                });

                await connection.ExecuteAsync(insertSql, nameAliases, transaction);
            }
        }

        /// <summary>
        /// 记录变更日志（内部方法）
        /// </summary>
        private async Task<bool> LogChangeAsync(IDbConnection connection, IDbTransaction? transaction, BankInfoChangeLog log)
        {
            var sql = @"
                INSERT INTO BankInfoChangeLog (BankId, ChangeType, TableName, FieldName, OldValue, NewValue, CreatedAt, CreatedBy)
            VALUES (@BankId, @ChangeType, @TableName, @FieldName, @OldValue, @NewValue, @CreatedAt, @CreatedBy)";

            var result = await connection.ExecuteAsync(sql, log, transaction);
            return result > 0;
        }

        /// <summary>
        /// 记录字段变更
        /// </summary>
        private async Task LogFieldChangesAsync(IDbConnection connection, IDbTransaction transaction, 
            BankInfo originalBank, BankInfo updatedBank)
        {
            var changes = new List<BankInfoChangeLog>();
            var now = DateTime.Now;

            // 检查各个字段的变更
            if (originalBank.StandardName != updatedBank.StandardName)
            {
                changes.Add(new BankInfoChangeLog
                {
                    BankId = updatedBank.Id,
                    ChangeType = "UPDATE",
                    TableName = "BankInfo",
                    FieldName = "StandardName",
                    OldValue = originalBank.StandardName,
                    NewValue = updatedBank.StandardName,
                    CreatedAt = now,
                    CreatedBy = updatedBank.UpdatedBy
                });
            }

            if (originalBank.ShortName != updatedBank.ShortName)
            {
                changes.Add(new BankInfoChangeLog
                {
                    BankId = updatedBank.Id,
                    ChangeType = "UPDATE",
                    TableName = "BankInfo",
                    FieldName = "ShortName",
                    OldValue = originalBank.ShortName,
                    NewValue = updatedBank.ShortName,
                    CreatedAt = now,
                    CreatedBy = updatedBank.UpdatedBy
                });
            }

            if (originalBank.IsActive != updatedBank.IsActive)
            {
                changes.Add(new BankInfoChangeLog
                {
                    BankId = updatedBank.Id,
                    ChangeType = "UPDATE",
                    TableName = "BankInfo",
                    FieldName = "IsActive",
                    OldValue = originalBank.IsActive.ToString(),
                    NewValue = updatedBank.IsActive.ToString(),
                    CreatedAt = now,
                    CreatedBy = updatedBank.UpdatedBy
                });
            }

            // 批量插入变更记录
            if (changes.Any())
            {
                var sql = @"
                    INSERT INTO BankInfoChangeLog (BankId, ChangeType, TableName, FieldName, OldValue, NewValue, CreatedAt, CreatedBy)
            VALUES (@BankId, @ChangeType, @TableName, @FieldName, @OldValue, @NewValue, @CreatedAt, @CreatedBy)";

                await connection.ExecuteAsync(sql, changes, transaction);
            }
        }

        #endregion
    }
}